package io.github.talelin.latticy.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.github.talelin.latticy.dto.PointsAdjustDTO;
import io.github.talelin.latticy.dto.PointsRecordDTO;
import io.github.talelin.latticy.mapper.PointsRecordMapper;
import io.github.talelin.latticy.model.PointsRecordDO;
import io.github.talelin.latticy.model.CUserDO;
import io.github.talelin.latticy.service.CUserService;
import io.github.talelin.latticy.service.PointsRecordService;
import io.github.talelin.latticy.vo.PointsRecordVO;
import io.github.talelin.latticy.vo.PointsStatsVO;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 积分记录服务实现类
 * @author talelin
 */
@Service
public class PointsRecordServiceImpl extends ServiceImpl<PointsRecordMapper, PointsRecordDO> implements PointsRecordService {

    @Autowired
    private CUserService cUserService;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    


    @Override
    @Transactional
    public PointsRecordDO addPoints(PointsRecordDTO dto) {
        // 获取当前积分余额
        Integer currentBalance = getCurrentBalance(dto.getUserId());
        
        // 创建积分记录
        PointsRecordDO record = PointsRecordDO.builder()
                .userId(dto.getUserId())
                .type(dto.getType())
                .points(dto.getPoints())
                .title(dto.getTitle())
                .description(dto.getDescription())
                .source(dto.getSource())
                .orderId(dto.getOrderId())
                .status(dto.getStatus())
                .balanceBefore(currentBalance)
                .balanceAfter(currentBalance + dto.getPoints())
                .expireDate(calculateExpireDate())
                .extra(dto.getExtra())
                .build();
        
        save(record);
        return record;
    }

    @Override
    @Transactional
    public PointsRecordDO deductPoints(PointsRecordDTO dto) {
        // 检查积分余额是否足够
        Integer currentBalance = getCurrentBalance(dto.getUserId());
        if (currentBalance < dto.getPoints()) {
            throw new RuntimeException("积分余额不足");
        }
        
        // 创建积分记录
        PointsRecordDO record = PointsRecordDO.builder()
                .userId(dto.getUserId())
                .type(dto.getType())
                .points(dto.getPoints())
                .title(dto.getTitle())
                .description(dto.getDescription())
                .source(dto.getSource())
                .orderId(dto.getOrderId())
                .status(dto.getStatus())
                .balanceBefore(currentBalance)
                .balanceAfter(currentBalance - dto.getPoints())
                .extra(dto.getExtra())
                .build();
        
        save(record);
        return record;
    }

    @Override
    @Transactional
    public PointsRecordDO adjustPoints(PointsAdjustDTO dto) {
        Integer currentBalance = getCurrentBalance(dto.getUserId());
        Integer newBalance;
        String type;
        
        if ("add".equals(dto.getAdjustType())) {
            newBalance = currentBalance + dto.getPoints();
            type = "earned";
        } else if ("subtract".equals(dto.getAdjustType())) {
            if (currentBalance < dto.getPoints()) {
                throw new RuntimeException("积分余额不足");
            }
            newBalance = currentBalance - dto.getPoints();
            type = "used";
        } else {
            throw new RuntimeException("无效的调整类型");
        }
        
        // 创建积分记录
        PointsRecordDO record = PointsRecordDO.builder()
                .userId(dto.getUserId())
                .type(type)
                .points(dto.getPoints())
                .title("管理员调整积分")
                .description(dto.getDescription())
                .source("admin")
                .status("success")
                .balanceBefore(currentBalance)
                .balanceAfter(newBalance)
                .extra("{\"reason\":\"" + dto.getReason() + "\",\"operator\":\"" + dto.getOperatorName() + "\"}")
                .build();
        
        save(record);
        
        // 更新C端用户表中的积分余额
        CUserDO cUser = cUserService.getById(dto.getUserId().intValue());
        if (cUser != null) {
            cUser.setPoints(newBalance);
            cUserService.updateById(cUser);
        }
        
        return record;
    }

    @Override
    public PointsRecordDO getRecordDetail(Integer recordId) {
        return getById(recordId);
    }

    @Override
    public IPage<PointsRecordVO> getUserRecords(Long userId, Integer page, Integer count, String type, String source) {
        Page<PointsRecordDO> pageParam = new Page<>(page, count);
        
        List<PointsRecordDO> records;
        if (type != null && !type.isEmpty()) {
            records = baseMapper.selectByUserIdAndType(userId, type, (page - 1) * count, count);
        } else if (source != null && !source.isEmpty()) {
            records = baseMapper.selectByUserIdAndSource(userId, source, (page - 1) * count, count);
        } else {
            records = baseMapper.selectByUserId(userId, (page - 1) * count, count);
        }
        
        // 转换为VO
        List<PointsRecordVO> voList = records.stream().map(this::convertToVO).collect(Collectors.toList());
        
        IPage<PointsRecordVO> result = new Page<>(page, count);
        result.setRecords(voList);
        result.setTotal(records.size()); // 这里应该查询总数，暂时简化处理
        
        return result;
    }

    @Override
    public PointsStatsVO getUserPointsStats(Long userId) {
        Integer currentBalance = getCurrentBalance(userId);
        Integer totalEarned = baseMapper.sumEarnedPoints(userId);
        Integer totalUsed = baseMapper.sumUsedPoints(userId);
        Integer totalExpired = baseMapper.sumExpiredPoints(userId);
        
        // 查询即将过期的积分
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime expireDate = now.plusDays(30);
        List<PointsRecordDO> expiringRecords = baseMapper.selectExpiringPoints(userId, now, expireDate);
        Integer expiringPoints = expiringRecords.stream().mapToInt(PointsRecordDO::getPoints).sum();
        
        PointsStatsVO stats = new PointsStatsVO();
        stats.setUserId(userId);
        stats.setCurrentPoints(currentBalance);
        stats.setTotalEarned(totalEarned);
        stats.setTotalUsed(totalUsed);
        stats.setTotalExpired(totalExpired);
        stats.setExpiringPoints(expiringPoints);
        
        return stats;
    }

    @Override
    public List<PointsRecordDO> getExpiringPoints(Long userId) {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime expireDate = now.plusDays(30);
        return baseMapper.selectExpiringPoints(userId, now, expireDate);
    }

    @Override
    @Transactional
    public void handleExpiredPoints() {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime expireDate = now.plusDays(30);
        
        // 查询所有即将过期的积分记录
        List<PointsRecordDO> allExpiringRecords = baseMapper.selectExpiringPoints(null, now, expireDate);
        
        for (PointsRecordDO record : allExpiringRecords) {
            if (record.getExpireDate().isBefore(now)) {
                // 创建过期记录
                Integer currentBalance = getCurrentBalance(record.getUserId());
                PointsRecordDO expiredRecord = PointsRecordDO.builder()
                        .userId(record.getUserId())
                        .type("expired")
                        .points(record.getPoints())
                        .title("积分过期")
                        .description("积分已过期：" + record.getTitle())
                        .source("expired")
                        .orderId(record.getOrderId())
                        .status("expired")
                        .balanceBefore(currentBalance)
                        .balanceAfter(currentBalance - record.getPoints())
                        .build();
                
                save(expiredRecord);
            }
        }
    }

    @Override
    public boolean checkPointsBalance(Long userId, Integer requiredPoints) {
        Integer currentBalance = getCurrentBalance(userId);
        return currentBalance >= requiredPoints;
    }

    @Override
    public Integer getCurrentBalance(Long userId) {
        Integer balance = baseMapper.selectCurrentBalance(userId);
        return balance != null ? balance : 0;
    }

    @Override
    public List<PointsRecordDO> getRecordsByOrderId(Long orderId) {
        return baseMapper.selectByOrderId(orderId);
    }

    @Override
    public IPage<PointsStatsVO> getUserPointsList(Integer page, Integer count, String username, Integer minPoints, Integer maxPoints, String status) {
        try {
            // 使用SQL聚合查询优化性能
            return getUserPointsListBySql(page, count, username, minPoints, maxPoints, status);
        } catch (Exception e) {
            // 如果SQL查询失败，回退到原来的方法
            System.err.println("SQL查询失败，回退到原方法: " + e.getMessage());
            return getUserPointsListFallback(page, count, username, minPoints, maxPoints, status);
        }
    }
    
    /**
     * 使用SQL聚合查询获取用户积分列表（高效版本）
     */
    private IPage<PointsStatsVO> getUserPointsListBySql(Integer page, Integer count, String username, Integer minPoints, Integer maxPoints, String status) {
        // 构建查询SQL
        String sql = buildUserPointsQuery(username, minPoints, maxPoints, status, count, (page - 1) * count);
        
        // 执行查询
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);
        
        // 转换为VO对象
        List<PointsStatsVO> statsList = results.stream()
                .map(this::convertMapToPointsStatsVO)
                .collect(Collectors.toList());
        
        // 获取总数
        String countSql = buildUserPointsCountQuery(username, minPoints, maxPoints, status);
        Long total = jdbcTemplate.queryForObject(countSql, Long.class);
        
        // 创建分页结果
        Page<PointsStatsVO> pageParam = new Page<>(page, count);
        pageParam.setRecords(statsList);
        pageParam.setTotal(total != null ? total : 0);
        
        return pageParam;
    }
    
    /**
     * 构建用户积分查询SQL
     */
    private String buildUserPointsQuery(String username, Integer minPoints, Integer maxPoints, String status, Integer limit, Integer offset) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("    u.id, ");
        sql.append("    u.nickname, ");
        sql.append("    u.mobile, ");
        sql.append("    u.real_name as realName, ");
        sql.append("    COALESCE(u.points, 0) as currentPoints, ");
        sql.append("    COALESCE(earned_stats.total_earned, 0) as totalEarned, ");
        sql.append("    COALESCE(used_stats.total_used, 0) as totalUsed, ");
        sql.append("    COALESCE(expired_stats.total_expired, 0) as totalExpired, ");
        sql.append("    COALESCE(expiring_stats.expiring_points, 0) as expiringPoints ");
        sql.append("FROM user u ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as total_earned ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE type = 'earned' AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") earned_stats ON u.id = earned_stats.user_id ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as total_used ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE type = 'used' AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") used_stats ON u.id = used_stats.user_id ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as total_expired ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE type = 'expired' AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") expired_stats ON u.id = expired_stats.user_id ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as expiring_points ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE type = 'earned' AND expire_date IS NOT NULL ");
        sql.append("    AND expire_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) ");
        sql.append("    AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") expiring_stats ON u.id = expiring_stats.user_id ");
        sql.append("WHERE u.delete_time IS NULL ");
        
        // 添加筛选条件
        if (username != null && !username.trim().isEmpty()) {
            sql.append("AND u.nickname LIKE '%").append(username).append("%' ");
        }
        
        if (minPoints != null) {
            sql.append("AND COALESCE(u.points, 0) >= ").append(minPoints).append(" ");
        }
        
        if (maxPoints != null) {
            sql.append("AND COALESCE(u.points, 0) <= ").append(maxPoints).append(" ");
        }
        
        // 根据状态筛选
        if (status != null && !status.trim().isEmpty()) {
            if ("low".equals(status)) {
                sql.append("AND COALESCE(u.points, 0) < 100 ");
            } else if ("high".equals(status)) {
                sql.append("AND COALESCE(u.points, 0) >= 100 ");
            }
        }
        
        sql.append("ORDER BY COALESCE(u.points, 0) DESC ");
        sql.append("LIMIT ").append(limit).append(" OFFSET ").append(offset);
        
        return sql.toString();
    }
    
    /**
     * 构建用户积分总数查询SQL
     */
    private String buildUserPointsCountQuery(String username, Integer minPoints, Integer maxPoints, String status) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) ");
        sql.append("FROM user u ");
        sql.append("WHERE u.delete_time IS NULL ");
        
        if (username != null && !username.trim().isEmpty()) {
            sql.append("AND u.nickname LIKE '%").append(username).append("%' ");
        }
        
        if (minPoints != null) {
            sql.append("AND COALESCE(u.points, 0) >= ").append(minPoints).append(" ");
        }
        
        if (maxPoints != null) {
            sql.append("AND COALESCE(u.points, 0) <= ").append(maxPoints).append(" ");
        }
        
        if (status != null && !status.trim().isEmpty()) {
            if ("low".equals(status)) {
                sql.append("AND COALESCE(u.points, 0) < 100 ");
            } else if ("high".equals(status)) {
                sql.append("AND COALESCE(u.points, 0) >= 100 ");
            }
        }
        
        return sql.toString();
    }
    
    /**
     * 将Map转换为PointsStatsVO
     */
    private PointsStatsVO convertMapToPointsStatsVO(Map<String, Object> row) {
        PointsStatsVO stats = new PointsStatsVO();
        
        stats.setId(getLongValue(row.get("id")));
        stats.setUsername(getStringValue(row.get("nickname")));
        stats.setNickname(getStringValue(row.get("nickname")));
        
        stats.setCurrentPoints(getIntegerValue(row.get("currentPoints")));
        stats.setTotalEarned(getIntegerValue(row.get("totalEarned")));
        stats.setTotalUsed(getIntegerValue(row.get("totalUsed")));
        stats.setTotalExpired(getIntegerValue(row.get("totalExpired")));
        stats.setExpiringPoints(getIntegerValue(row.get("expiringPoints")));
        
        // 设置前端兼容字段
        stats.setPoints(stats.getCurrentPoints());
        stats.setTotalEarnedPoints(stats.getTotalEarned());
        stats.setTotalUsedPoints(stats.getTotalUsed());
        
        return stats;
    }
    
    /**
     * 回退方法：使用原来的查询方式
     */
    private IPage<PointsStatsVO> getUserPointsListFallback(Integer page, Integer count, String username, Integer minPoints, Integer maxPoints, String status) {
        // 创建分页参数
        Page<PointsStatsVO> pageParam = new Page<>(page, count);
        
        // 查询用户列表（这里简化处理，实际应该根据条件筛选）
        List<CUserDO> users = cUserService.list();
        
        // 为每个用户查询积分统计
        List<PointsStatsVO> statsList = users.stream()
                .map(user -> {
                    PointsStatsVO stats = getUserPointsStats(user.getId().longValue());
                    // 设置用户基本信息
                    stats.setUsername(user.getNickname()); // 使用nickname作为显示名称
                    stats.setNickname(user.getNickname());
                    
                    // 设置前端兼容字段
                    stats.setId(user.getId().longValue());
                    stats.setPoints(stats.getCurrentPoints());
                    stats.setTotalEarnedPoints(stats.getTotalEarned());
                    stats.setTotalUsedPoints(stats.getTotalUsed());
                    
                    return stats;
                })
                .filter(stats -> {
                    // 根据积分范围筛选
                    if (minPoints != null && stats.getCurrentPoints() < minPoints) {
                        return false;
                    }
                    if (maxPoints != null && stats.getCurrentPoints() > maxPoints) {
                        return false;
                    }
                    // 根据用户昵称筛选
                    if (username != null && !username.isEmpty()) {
                        return stats.getNickname() != null && 
                               stats.getNickname().toLowerCase().contains(username.toLowerCase());
                    }
                    return true;
                })
                .collect(Collectors.toList());
        
        // 计算分页
        int total = statsList.size();
        int start = (page - 1) * count;
        int end = Math.min(start + count, total);
        
        if (start < total) {
            List<PointsStatsVO> pageData = statsList.subList(start, end);
            pageParam.setRecords(pageData);
        }
        
        pageParam.setTotal(total);
        pageParam.setCurrent(page);
        pageParam.setSize(count);
        
        return pageParam;
    }
    
    // 工具方法
    private String getStringValue(Object value) {
        return value != null ? value.toString() : null;
    }
    
    private Long getLongValue(Object value) {
        if (value == null) return null;
        if (value instanceof Number) {
            return ((Number) value).longValue();
        }
        try {
            return Long.parseLong(value.toString());
        } catch (NumberFormatException e) {
            return null;
        }
    }
    
    private Integer getIntegerValue(Object value) {
        if (value == null) return 0;
        if (value instanceof Number) {
            return ((Number) value).intValue();
        }
        try {
            return Integer.parseInt(value.toString());
        } catch (NumberFormatException e) {
            return 0;
        }
    }

    @Override
    @Transactional
    public String initUsersPoints(Integer defaultPoints) {
        // 获取所有用户
        List<CUserDO> users = cUserService.list();
        int count = 0;
        
        for (CUserDO user : users) {
            // 检查用户是否已有积分记录
            Integer currentBalance = getCurrentBalance(user.getId().longValue());
            if (currentBalance == 0) {
                // 为用户创建初始积分记录
                PointsRecordDO record = PointsRecordDO.builder()
                        .userId(user.getId().longValue())
                        .type("earned")
                        .points(defaultPoints)
                        .title("初始积分")
                        .description("系统为用户设置的初始积分")
                        .source("system")
                        .status("success")
                        .balanceBefore(0)
                        .balanceAfter(defaultPoints)
                        .build();
                
                save(record);
                count++;
            }
        }
        
        return String.format("成功为 %d 个用户初始化积分，每人 %d 积分", count, defaultPoints);
    }

    /**
     * 转换为VO
     */
    private PointsRecordVO convertToVO(PointsRecordDO record) {
        PointsRecordVO vo = new PointsRecordVO();
        BeanUtils.copyProperties(record, vo);
        return vo;
    }

    /**
     * 计算积分过期时间（默认1年后）
     */
    private LocalDateTime calculateExpireDate() {
        return LocalDateTime.now().plusYears(1);
    }
}
